package com.lcredu.spring.jdbc;

import com.lcredu.bean.Monster;
import com.lcredu.jdbctemplate.dao.MonsterDao;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @author 黎长容
 * @date 2025-07-17 12:39
 */
public class JdbcConnection {

    @Test
    public void getCon() throws SQLException {
        ApplicationContext ioc
                = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        DataSource dataSource = ioc.getBean(DataSource.class);
        Connection connection = dataSource.getConnection();
        System.out.println("connection=" + connection);
        connection.close();
    }

    //测试增加功能
    @Test
    public void add() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        JdbcTemplate jdbcTemplate = ioc.getBean("jdbcTemplate", JdbcTemplate.class);
        //1.第一种添加方式:execute
//        String sql = "insert into monster values (401,'红孩儿','火尖枪');";
//        jdbcTemplate.execute(sql);

        //2.第二种添加方式:update
        String sql = "insert into monster values (?,?,?);";
        int updated = jdbcTemplate.update(sql, 500, "资本家", "吸血");
        System.out.println("添加成功条数=" + updated);
    }


    @Test
    public void update() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        JdbcTemplate jdbcTemplate = ioc.getBean("jdbcTemplate", JdbcTemplate.class);

        String updateSql = "update monster set name = ? where id = ?";

        int updated = jdbcTemplate.update(updateSql, "万恶的资本家", 500);

        System.out.println("修改成功条数=" + updated);
    }


    @Test
    public void batchDDL() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        JdbcTemplate jdbcTemplate = ioc.getBean("jdbcTemplate", JdbcTemplate.class);
        /**
         * 这里演示批量添加的思考过程
         * 推测API,组织条件参数,学以致用
         */
        String sql = "insert into monster values (?,?,?);";
        List<Object[]> arrayList = new ArrayList<>();
        arrayList.add(new Object[]{"600", "老鼠精", "偷吃粮食"});
        arrayList.add(new Object[]{"700", "老猫精", "吃老鼠精"});
        int[] ints = jdbcTemplate.batchUpdate(sql, arrayList);
        for (int anInt : ints) {
            System.out.println("anInt=" + anInt);
        }
        System.out.println("batch...add...ok");
    }

    //目标:查询 id=100 的 monster 并封装到 Monster 实体对象
    @Test
    public void queryToMonster() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        JdbcTemplate jdbcTemplate = ioc.getBean("jdbcTemplate", JdbcTemplate.class);

        //public <T> T queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException
        //1.推测API(参数,返回值)
        //2.填充参数
        String querySql = "select id as monsterId, name, skill from monster where id = ?";
        RowMapper<Monster> rowMapper = new BeanPropertyRowMapper<>(Monster.class);
        Monster monster = jdbcTemplate.queryForObject(querySql, rowMapper, 100);
        System.out.println(monster);
    }

    //目标:查询 id>=200 的 monster 并封装到 Monster 实体对象
    @Test
    public void queryToMonsterList() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        JdbcTemplate jdbcTemplate = ioc.getBean("jdbcTemplate", JdbcTemplate.class);

        // public <T> List<T> query(String sql, RowMapper<T> rowMapper)
        String querySql = "select id as monsterId, name, skill from monster where id >= ?";
        RowMapper<Monster> rowMapper = new BeanPropertyRowMapper<>(Monster.class);
        List<Monster> monsterList = jdbcTemplate.query(querySql, rowMapper, 200);
        for (Monster monster : monsterList) {
            System.out.println(monster);
        }
        System.out.println("queryToMonsterList---ok...");
    }

    //目标:查询返回结果只有一行一列的值，比如查询 id=100 的怪物名
    @Test
    public void querySingle() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        JdbcTemplate jdbcTemplate = ioc.getBean("jdbcTemplate", JdbcTemplate.class);

        //public <T> T queryForObject(String sql, Class<T> requiredType)
//        String querySql = "select name from monster where id = ?";
//        String name = jdbcTemplate.queryForObject(querySql, String.class,100);
        String querySql = "select count(id) from monster where id > ?";
        Integer integer = jdbcTemplate.queryForObject(querySql, Integer.class, 300);
        //如果我要查一共有多少怪物呢?(id>400)
        System.out.println(integer);
    }

    //目标:使用 Map 传入具名参数完成操作，比如添加 螃蟹精.:name 就是具名参数形式需要使
    //用 NamedParameterJdbcTemplate 类
    //按照字段名顺序添加
    @Test
    public void testDataByNamedParameterJdbcTemplate() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = ioc.getBean("parameterJdbcTemplate", NamedParameterJdbcTemplate.class);

        //public int update(String sql, Map<String, ?> paramMap)
        String sql = "insert into monster values (:id6,:skill,:name)";
        HashMap<String, Object> mapParam = new HashMap<>();
        mapParam.put("id6", 1000);
        mapParam.put("name", "西瓜精2");
        mapParam.put("skill", "降暑");
        int updated = namedParameterJdbcTemplate.update(sql, mapParam);
        System.out.println("updated=" + updated);
    }

    //任务:使用 sqlparametersoruce 来封装具名参数,还是添加一个 Monster 狐狸精
    @Test
    public void operDataBySqlparametersoruce() {
        ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = ioc.getBean("parameterJdbcTemplate", NamedParameterJdbcTemplate.class);

        //public int update(String sql, SqlParameterSource paramSource)
        String sql = "insert into monster values (:monsterId,:skill,:name)";
        Monster monster = new Monster(2000, "狐狸精", "魅惑之术");
        SqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(monster);
        int updated = namedParameterJdbcTemplate.update(sql, beanPropertySqlParameterSource);
        System.out.println("updated=" + updated);
    }
    
    @Test
    public void testMonsterDao(){
        MonsterDao monsterDao = new ClassPathXmlApplicationContext("jdbcTemplate.xml").getBean(MonsterDao.class);
        monsterDao.save(new Monster(3000,"黎长容","学习"));
        System.out.println("ok...");
    }
}
